#include "materialservice.h"
#include "constant/DBTableConfig.h"
#include "util/database/sqlgenerator.h"
#include "util/database/databasemanager.h"
#include <QSqlQuery>
#include <QSqlRecord>
#include <QDebug>
#include <QDateTime>

using namespace DBTableConfig;

/**
 * @brief 构造函数：初始化表字段定义
 * 确保字段名与MaterialVO的属性名、数据库表字段完全一致，
 * 实现ORM映射的基础
 */
MaterialService::MaterialService(){}


/**
 * @brief 插入物料记录
 * 将MaterialVO对象转换为数据库字段格式，自动处理时间字段，
 * 确保数据正确写入数据库
 */
bool MaterialService::saveMaterial(const MaterialVO* vo, int oper_num, QString* errorMsg)
{
    // todo：需要改下查询条件，以及查询表
    //查询表
    QString tableName = QString("%1, %2, %3, %4")
            .arg(MATERIAL_TABLE, WAREHOUSE_TABLE, SHELF_TABLE, INVENTORY_TABLE);

    //查询字段
    QStringList fields = {QString("%1.id as inventory_id").arg(INVENTORY_TABLE), "material_id", "shelf_id","warehouse_id",
                          "material_name", "model", "image", "inventory_quentity","material_quantity",
                          "warehouse_name", "shelf_name", QString("%1.create_time").arg(INVENTORY_TABLE),
                          QString("%1.update_time").arg(INVENTORY_TABLE)};

    //查询条件
    QString condition = QString("material_id = %1 and shelf_id = %2 and warehouse_id = %3")
            .arg(vo->get_material_id())
            .arg(vo->get_shelf_id())
            .arg(vo->get_warehouse_id());
    //生成sql语句
    QString selectSql = SqlGenerator::generateSelect(tableName, fields, condition);
    if (selectSql.isEmpty()) {
        if (errorMsg) *errorMsg = "生成查询SQL失败";
        return false;
    }

    if (!DatabaseManager::getInstance()->isConnected()) {
        if (errorMsg) *errorMsg = "数据库未连接";
        return false;
    }

    // 获取当前时间
    QString currentDate = QDateTime::currentDateTime().toString("yyyy-MM-dd HH:mm:ss");
    QString oper_symbol = oper_num > 0 ? "+" : "-";
    //先查询数据是否存在
    QSqlQuery query = DatabaseManager::getInstance()->executeQuery(selectSql, errorMsg);
    QList<MaterialVO*> materialVOList = queryToVOList(query);
    if(materialVOList.size() > 0){

        // 库存物料修改
        QVariantMap updateInventoryData = { {"inventory_quantity", QString("inventory_quantity + %1").arg(oper_num)},
                                            {"update_time", currentDate} };
        QString updateInventorySql = SqlGenerator::generateUpdate(INVENTORY_TABLE, updateInventoryData, QString("id = %1").arg(vo->get_inventory_id()));
        DatabaseManager::getInstance()->executeSql(updateInventorySql, errorMsg);

        // 物料数量修改
        QVariantMap updateMaterialData = { {"material_quantity", QString("material_quantity %1 %2").arg(oper_symbol).arg(oper_num)},
                                           {"update_time", currentDate}};
        QString updateMaterialSql = SqlGenerator::generateUpdate(WAREHOUSE_TABLE, updateMaterialData, QString("id = %1").arg(vo->get_material_id()));
        DatabaseManager::getInstance()->executeSql(updateMaterialSql, errorMsg);

        // 仓库、货架物料修改
        updateSWMaterial(vo, oper_num, errorMsg);

    }else{

        // 执行更新操作
        QString selectMaterialSql = SqlGenerator::generateSelect(MATERIAL_TABLE, QStringList(),
                                                                 QString("id = %1").arg(vo->get_material_id()));
        QSqlQuery query = DatabaseManager::getInstance()->executeQuery(selectMaterialSql, errorMsg);
        if(query.next()){
            // 物料数量修改
            QVariantMap updateMaterialData = { {"material_quantity", QString("material_quantity + %1").arg(oper_num)},
                                               {"update_time", currentDate} };
            QString updateMaterialSql = SqlGenerator::generateUpdate(MATERIAL_TABLE, updateMaterialData, QString("id = %1").arg(vo->get_material_id()));
            DatabaseManager::getInstance()->executeSql(updateMaterialSql, errorMsg);
        }else{
            // 执行插入操作
            QVariantMap insertMaterialData = { {"material_name", vo->get_material_name()},
                                       {"model", vo->get_model()},
                                       {"image", vo->get_image()},
                                       {"material_quantity", vo->get_material_quantity()},
                                       {"create_time", currentDate},
                                       {"update_time", currentDate}};
            QString insertMaterialSql = SqlGenerator::generateInsert(MATERIAL_TABLE, insertMaterialData);
            DatabaseManager::getInstance()->executeSql(insertMaterialSql, errorMsg);
        }

        // 库存物料信息插入
        QVariantMap insertInventoryData = { {"material_id", vo->get_material_id()},
                                            {"shelf_id", vo->get_shelf_id()},
                                            {"warehouse_id", vo->get_warehouse_id()},
                                            {"inventory_quantity", vo->get_inventory_quantity()},
                                            {"create_time", currentDate},
                                            {"update_time", currentDate} };
        QString updateInventorySql = SqlGenerator::generateInsert(INVENTORY_TABLE, insertInventoryData);
        DatabaseManager::getInstance()->executeSql(updateInventorySql, errorMsg);

        // 仓库、货架物料修改
        updateSWMaterial(vo, oper_num, errorMsg);
    }
    return true;
}

/**
 * @brief 更新仓库和货架物料数量
 * 基于MaterialVO中的ID定位记录，更新其他字段值，
 * 自动更新时间戳确保数据一致性
 */
bool MaterialService::updateSWMaterial(const MaterialVO* vo, int oper_num, QString* errorMsg)
{
    if (!DatabaseManager::getInstance()->isConnected()) {
        if (errorMsg) *errorMsg = "数据库未连接";
        return false;
    }

    // 验证ID有效性
    if (vo->get_inventory_id() <= 0) {
        if (errorMsg) *errorMsg = "无效的物料ID";
        return false;
    }

    // 当前时间
    QString currentDate = QDateTime::currentDateTime().toString("yyyy-MM-dd HH:mm:ss");
    QString oper_symbol = oper_num > 0 ? "+" : "-";
    // 仓库物料数量修改
    QVariantMap updateWarehouseData = { {"warehouse_quantity", QString("warehouse_quantity %1 %2").arg(oper_symbol).arg(oper_num)},
                                        {"update_time", currentDate} };
    QString updateWarehouseSql = SqlGenerator::generateUpdate(WAREHOUSE_TABLE, updateWarehouseData, QString("id = %1").arg(vo->get_warehouse_id()));
    DatabaseManager::getInstance()->executeSql(updateWarehouseSql, errorMsg);
    // 货架物料修改
    QVariantMap updateShelfData = { {"shelf_quantity", QString("shelf_quantity %1 %2").arg(oper_symbol).arg(oper_num)},
                                    {"update_time", currentDate} };
    QString updateShelfSql = SqlGenerator::generateUpdate(SHELF_TABLE, updateShelfData, QString("id = %1").arg(vo->get_shelf_id()));
    DatabaseManager::getInstance()->executeSql(updateShelfSql, errorMsg);

    return true;
}


/**
 * @brief 根据ID查询物料
 * 通过主键ID查询单条物料记录，返回MaterialVO对象
 */
QList<MaterialVO*> MaterialService::getMaterialById(int inventory_id, QString* errorMsg)
{
    if (!DatabaseManager::getInstance()->isConnected()) {
        if (errorMsg) *errorMsg = "数据库未连接";
        return {};
    }

    if (inventory_id <= 0) {
        if (errorMsg) *errorMsg = "无效的物料ID";
        return {};
    }

    //查询表
    QString tableName = QString("%1, %2, %3, %4")
            .arg(MATERIAL_TABLE, WAREHOUSE_TABLE, SHELF_TABLE, INVENTORY_TABLE);
    //查询字段
    QStringList fields = {QString("%1.id as inventory_id").arg(INVENTORY_TABLE), "material_id", "shelf_id","warehouse_id",
                          "material_name", "model", "image", "inventory_quentity","material_quantity",
                          "warehouse_name", "shelf_name", QString("%1.create_time").arg(INVENTORY_TABLE),
                          QString("%1.update_time").arg(INVENTORY_TABLE)};

    //查询条件
    QString condition = QString("%1.id = %2").arg(INVENTORY_TABLE).arg(inventory_id);
    //生成sql语句
    QString selectSql = SqlGenerator::generateSelect(tableName, fields, condition);

    if (selectSql.isEmpty()) {
        if (errorMsg) *errorMsg = "生成查询SQL失败";
        return {};
    }

    // 执行查询并转换结果
    QSqlQuery query = DatabaseManager::getInstance()->executeQuery(selectSql, errorMsg);
    return queryToVOList(query);
}

/**
 * @brief 按条件查询物料
 * 支持复杂查询条件、排序和分页，返回符合条件的MaterialVO列表
 */
QList<MaterialVO*> MaterialService::getMaterialsByCondition(const MaterialVO& vo, QString* errorMsg)
{
    //查询表
    QString tableName = QString("%1, %2, %3, %4")
            .arg(MATERIAL_TABLE, WAREHOUSE_TABLE, SHELF_TABLE, INVENTORY_TABLE);
    //查询字段
    QStringList fields = {QString("%1.id as inventory_id").arg(INVENTORY_TABLE), "material_id", "shelf_id",
                          QString("%1.warehouse_id").arg(INVENTORY_TABLE), "material_name", "model", "image",
                          "inventory_quantity", "material_quantity","warehouse_name", "shelf_name",
                          QString("%1.create_time").arg(INVENTORY_TABLE), QString("%1.update_time").arg(INVENTORY_TABLE)};

    //查询条件
    QString condition = QString("material_name like ('%%1%') and shelf_name like ('%%2%') and warehouse_name like ('%%3%')")
            .arg(vo.get_material_name(), vo.get_shelf_name(), vo.get_warehouse_name());
    //生成sql语句
    QString selectSql = SqlGenerator::generateSelect(tableName, fields, condition);

    QList<MaterialVO*> materialVOList;

    if (selectSql.isEmpty()) {
        if (errorMsg) *errorMsg = "生成查询SQL失败";
        return materialVOList;
    }

    if (!DatabaseManager::getInstance()->isConnected()) {
        if (errorMsg) *errorMsg = "数据库未连接";
        return materialVOList;
    }

    QSqlQuery query = DatabaseManager::getInstance()->executeQuery(selectSql, errorMsg);
    materialVOList = queryToVOList(query);
    return materialVOList;
}

/**
 * @brief 将查询结果转换为MaterialVO指针列表
 * 从SQL查询结果中提取字段值，逐一填充到MaterialVO对象，
 * 处理日期时间等特殊类型的转换
 */
QList<MaterialVO*> MaterialService::queryToVOList(QSqlQuery& query)
{
    QList<MaterialVO*> resultList;  // 修改为指针列表

    while (query.next()) {
        // 使用new创建对象，避免栈上的对象拷贝
        MaterialVO* vo = new MaterialVO();

        // 填充基本属性（与数据库字段一一对应）
        vo->set_inventory_id(query.value("inventory_id").toInt());
        vo->set_material_id(query.value("material_id").toInt());
        vo->set_shelf_id(query.value("shelf_id").toInt());
        vo->set_warehouse_id(query.value("warehouse_id").toInt());
        vo->set_material_name(query.value("material_name").toString());
        vo->set_model(query.value("model").toString());
        vo->set_image(query.value("image").toString());
        vo->set_inventory_quantity(query.value("inventory_quantity").toInt());
        vo->set_material_quantity(query.value("material_quantity").toInt());
        vo->set_warehouse_name(query.value("warehouse_name").toString());
        vo->set_shelf_name(query.value("shelf_name").toString());

        // 处理日期时间类型
        vo->set_create_time(query.value("create_time").toDateTime());
        vo->set_update_time(query.value("update_time").toDateTime());

        resultList.append(vo);  // 现在append的是指针，不需要拷贝对象
    }

    return resultList;
}

/**
 * @brief 将MaterialVO转换为数据库操作的键值对
 * 提取VO中的属性值，按数据库字段名组织，
 * 为SQL生成提供数据支持
 */
QVariantMap MaterialService::voToMap(const MaterialVO& vo)
{
    QVariantMap data;

    // 仅包含需要持久化的字段（与数据库表字段对应）
    if (vo.get_inventory_id() > 0) {
        data["inventory_id"] = vo.get_inventory_id();  // 更新操作需要ID
    }
    data["material_id"] = vo.get_material_id();
    data["shelf_id"] = vo.get_shelf_id();
    data["warehouse_id"] = vo.get_warehouse_id();
    data["material_name"] = vo.get_material_name();
    data["model"] = vo.get_model();
    data["image"] = vo.get_image();
    data["inventory_quantity"] = vo.get_inventory_quantity();
    data["material_quantity"] = vo.get_material_quantity();
    data["warehouse_name"] = vo.get_warehouse_name();
    data["shelf_name"] = vo.get_shelf_name();

    // 时间字段处理：插入时自动生成，更新时保留创建时间
    if (vo.get_create_time().isValid()) {
        data["create_time"] = vo.get_create_time();
    }
    data["update_time"] = vo.get_update_time().isValid() ?
                vo.get_update_time() : QDateTime::currentDateTime();

    return data;
}
